package sql;

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Iterator;
import java.util.Map;
import java.util.Vector;

import main.Configuration;

public class DBConnection {
	
	private static DBConnection Instance = null;
	private Connection connection = null;
	private boolean initialized = false;
	private String tableName = "tabletest";
	
	private DBConnection() {
		init();
		
	}
	
	private void init() {
		
		if(!initialized) {
			System.out.println("-------- PostgreSQL " + "JDBC Connection Testing ------------");
			 
			try {
	 			Class.forName("org.postgresql.Driver");
	 
			} catch (ClassNotFoundException e) {
	 
				System.out.println("Where is your PostgreSQL JDBC Driver? "	+ "Include in your library path!");
				e.printStackTrace();
	 
			}
	 
			System.out.println("PostgreSQL JDBC Driver Registered!");
	 
			try {
	 
				// "jdbc:postgresql://196.168.6.55:5432/DAFdatasource", "coflight", "password");
				connection = DriverManager.getConnection( Configuration.POSTGRES_JDBC_CONNECTION, Configuration.POSTGRES_USER, Configuration.POSTGRES_PASSWORD);
	 
			} catch (SQLException e) {
	 
				System.out.println("Connection Failed! Check output console");
				e.printStackTrace();
			}
	 
			if (connection != null) {
				System.out.println("You made it, take control your database now!");
				initialized = true;
			} else {
				System.out.println("Failed to make connection!");
			}
			
		} else {
			System.out.println("Connection is already init!");
		}
	}
	
	public void close() {
		
        if (connection != null) {
    		try {
    			connection.close();
    			connection 	= null;
    			initialized = false;
    			Instance = null;
    		} catch (SQLException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}
        }

		
	}
	
	public String getCreateTableString(final Vector<String> fieldToExtract) {
		
		String sql = "CREATE TABLE " + tableName + "(id serial,";
		
		for (Iterator iter = fieldToExtract.iterator(); iter.hasNext();) {

		    String fieldName = (String) iter.next();
		    
		    sql = sql + "\"" + fieldName + "\" VARCHAR(100),";
		}
		
		if(sql.endsWith(",")) {
			
			int sqllen = sql.length();
			sql = sql.substring(0, sqllen - 1 );
		}
		
		sql = sql + ")";
		
		return sql;
	}
	
	public void printResultSet(final ResultSet rs) {

		// PRINT RESULT SET
		if (null != rs) {

			
			try {
				ResultSetMetaData rsmd = rs.getMetaData();
			    int numCols = rsmd.getColumnCount();

			    while (rs.next()) {
				    StringBuffer sb = new StringBuffer("--> ");
					
					for (int i = 1; i <= numCols; i++) {
						sb.append( rs.getString(i)).append(" - ");
					}

				    System.out.println(sb.toString());
				}
			    
			    
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
	}
	
	public String getSampleSelect(final Vector<String> fieldToExtract) {
		StringBuffer sql = new StringBuffer("SELECT ");
		
		for (Iterator iter = fieldToExtract.iterator(); iter.hasNext();) {

		    String fieldName = (String) iter.next();
		    
		    sql.append("\"").append( fieldName ).append("\" ,");
		}
		
		sql.deleteCharAt( sql.length() - 1);
		
		sql.append( " FROM ").append( tableName ).append(" ");
		
		return sql.toString();
	}
	
	public String getCreateInsertString(final Map<String, String> mapFieldNameValue) {
		
		StringBuffer sql = new StringBuffer("INSERT INTO " + tableName + " (");

		// Create Intert string : begin
		
		// INSERT INTO products (product_no, name, price) VALUES ("1", "the name", "100" );
		// Conct the fields name
		// for (Map.Entry<String, String> entry : mapFieldNameValue.entrySet()) {
		//     String fieldName = entry.getKey();
		// }
		
		for (String fieldName : mapFieldNameValue.keySet()) {
		    
		    sql.append("\"").append( fieldName).append("\",");
		}

		sql.deleteCharAt(sql.length() - 1);
		
		sql.append( ") VALUES (");
		// Create Intert string : end

		for (String value : mapFieldNameValue.values()) {

		    sql.append("'").append( value).append("',");
		}

		sql.deleteCharAt(sql.length() - 1);
	    
		sql.append(");");
		
		return sql.toString();
	}

	public void buildInsert(final Vector< String> vectorInsert) {

        Statement st = null;

        try {

          st = connection.createStatement();
          
          connection.setAutoCommit(false);

			for (Iterator iter = vectorInsert.iterator(); iter.hasNext();) {
				String strsql = (String) iter.next();
				st.addBatch(strsql);
		        // System.out.println("buildInsert : strsql : " +  strsql);
				
			}

          
          int counts[] = st.executeBatch();

          connection.commit();

          System.out.println("Committed " + counts.length + " updates");

        } catch (SQLException ex) {

            System.out.println("buildInsert : Committed  SQLException : " +  ex.getNextException());
            
            if (connection != null) {
                try {
                	connection.rollback();
                } catch (SQLException ex1) {

                	System.out.println("buildInsert : rollback  SQLException : " + ex.getMessage());
                }
            }


        } finally {

            try {
 
                if (st != null) {
                    st.close();
                }

            } catch (SQLException ex) {
            	System.out.println("buildInsert : st.close()  SQLException : " + ex.getMessage());
            }
        }
    }

	public void buildTable(final String strsql) {

        System.out.println("buildTable : strsql : " +  strsql);

        Statement st = null;

        try {

          st = connection.createStatement();
          
          connection.setAutoCommit(false);
          
          st.addBatch("DROP TABLE IF EXISTS " + tableName);
          st.addBatch(strsql);

          int counts[] = st.executeBatch();

          connection.commit();

          System.out.println("buildTable : Committed " + counts.length + " updates");

        } catch (SQLException ex) {

            System.out.println("buildTable : Committed  SQLException : " +  ex.getNextException());
            
            if (connection != null) {
                try {
                	connection.rollback();
                } catch (SQLException ex1) {

                	System.out.println("buildTable : rollback  SQLException : " + ex.getMessage());
                }
            }


        } finally {

            try {
 
                if (st != null) {
                    st.close();
                }

            } catch (SQLException ex) {
            	System.out.println("buildTable : st.close()  SQLException : " + ex.getMessage());
            }
        }
    }
	
	public void createTable(final String strsql) {
		// "CREATE TABLE IF NOT EXISTS user(user_id SERIAL NOT NULL PRIMARY KEY,username varchar(225) NOT NULL UNIQUE,password varchar(225),islogged varchar(10))"
		PreparedStatement ps;
		
		try {
			ps = connection.prepareStatement(strsql);
			ps.executeUpdate();
			ps.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
	}
	
	 public ResultSet executeQuery(String query) {

        PreparedStatement pst = null;
        ResultSet rs = null;

        try {
            
            pst = connection.prepareStatement(query);
            rs = pst.executeQuery();
            
            // printResultSet(rs);

        } catch (SQLException ex) {
            System.out.println("executeQuery : SQLException : " +  ex.getNextException());

        } finally {

            try {
                if (pst != null) {
                    pst.close();
                }

            } catch (SQLException ex) {
                System.out.println("executeQuery : pst.close()  SQLException : " +  ex.getNextException());

            }
        }

        return rs;
        
	 }
	
	public static DBConnection getInstance() {
		
		if (null == Instance) {
			
			Instance = new DBConnection();
		}
		
		return Instance;
		
	}

}
